/**
 * Created by Neville Kadwa.
 */

package org.shiftone.cdep.store;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.sql.ResultSetMetaData;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;

import org.apache.log4j.Logger;
import org.shiftone.cdep.util.JDBCUtil;

public abstract class AbstractStoreManager implements StoreManager
{

    public static final Logger LOG = Logger.getLogger(AbstractStoreManager.class);

    protected Connection connection;

    AbstractStoreManager()
    {
    }

    protected abstract String prepareInsertQuery(String methodName, String query, boolean generateKey);
    protected abstract String prepareUpdateQuery(String methodName, String query);
    protected abstract String prepareSelectQuery(String methodName, String query);
    protected abstract String prepareDeleteQuery(String methodName, String query);

    /**
     * Must set parameter 1 to the insert ID and return the same ID
     */
    protected abstract int executeInsert(PreparedStatement stmt, boolean generateKey) throws SQLException;
    protected abstract void executeDelete(PreparedStatement stmt) throws SQLException;
    protected abstract ResultSet executeSelect(PreparedStatement stmt) throws SQLException;
    protected abstract void executeUpdate(PreparedStatement stmt) throws SQLException;

    public void setConnection(Connection connection)
    {
        this.connection = connection;
    }

    public Connection getConnection()
    {
        return this.connection;
    }

    protected void closeConnection()
    {

    }

    public void shutdown()
    {
    }

    public int setImportLocation(String importLocation) throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            String query = prepareSelectQuery("setImportLocation", "select import_location_id from cdep_import_loc where location_tx = ?");

            ps = conn.prepareStatement(query);

            ps.setString(1, importLocation);

            rs = executeSelect(ps);;

            if (rs.next())
            {
                return rs.getInt(1);
            }
            JDBCUtil.close(rs);
            rs = null;
            JDBCUtil.close(ps);
            ps = null;

            query = prepareInsertQuery("setImportLocation", "insert into cdep_import_loc (location_tx) values (?)", true);

            ps = conn.prepareStatement(query);

            ps.setString(1, importLocation);

            return executeInsert(ps, true);
        }
        catch (SQLException sex)
        {
            throw new StoreException("setImportLocation", sex);
        }
        finally
        {
            JDBCUtil.close(rs);
            JDBCUtil.close(ps);
            closeConnection();
        }
    }

    public int setPackage(String packageName, Integer parentId) throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            String query = prepareSelectQuery("setPackage", "select package_id from cdep_package where name_tx = ?");

            ps = conn.prepareStatement(query);

            ps.setString(1, packageName);

            rs = executeSelect(ps);;

            if (rs.next())
            {
                return rs.getInt(1);
            }
            JDBCUtil.close(rs);
            rs = null;
            JDBCUtil.close(ps);
            ps = null;

            query = prepareInsertQuery("setPackage", "insert into cdep_package (name_tx, parent_id) values (?,?)", true);

            ps = conn.prepareStatement(query);

            ps.setString(1, packageName);
            if (parentId != null)
                ps.setInt(2, parentId.intValue());
            else
                ps.setNull(2, Types.INTEGER);

            return executeInsert(ps, true);
        }
        catch (SQLException sex)
        {
            throw new StoreException("setPackage", sex);
        }
        finally
        {
            JDBCUtil.close(rs);
            JDBCUtil.close(ps);
            closeConnection();
        }
    }

    public void buildPackageTree() throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;

        try
        {
            LOG.debug("buildPackageTree: initialize");
            String query = prepareInsertQuery("buildPackageTree",
                "insert into cdep_package_tree (package_id, child_id) " +
                "select p.package_id, p.package_id " +
                "from cdep_package p " +
                "where not exists ( " +
                "    select * from cdep_package_tree " +
                "    where package_id = p.package_id " +
                "      and child_id = p.package_id " +
                ")", false);

            ps = conn.prepareStatement(query);

            int numUpdated = executeInsert(ps, false);

            if (numUpdated == 0)
                return;
            LOG.debug("buildPackageTree: added " + numUpdated + " packages");

            JDBCUtil.close(ps);
            ps = null;

            query = prepareInsertQuery("buildPackageTree",
                "insert into cdep_package_tree (package_id, child_id) " +
                "select pt.package_id, p.package_id " +
                "from cdep_package p " +
                "    ,cdep_package_tree pt " +
                "where pt.child_id = p.parent_id " +
                "  and not exists ( " +
                "    select * from cdep_package_tree " +
                "    where package_id = pt.package_id " +
                "      and child_id = p.package_id " +
                ")", false);

            ps = conn.prepareStatement(query);

            do
            {
                numUpdated = executeInsert(ps, false);
                LOG.debug("buildPackageTree: round added " + numUpdated + " children");
            }
            while (numUpdated > 0);

        }
        catch (SQLException sex)
        {
            throw new StoreException("buildPackageTree", sex);
        }
        finally
        {
            JDBCUtil.close(ps);
            closeConnection();
        }
    }

    public int getClass(String className) throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            String query = prepareSelectQuery("getClass", "select class_id from cdep_class where name_tx = ?");

            ps = conn.prepareStatement(query);

            ps.setString(1, className);

            rs = executeSelect(ps);

            if (rs.next())
            {
                return rs.getInt(1);
            }
        }
        catch (SQLException sex)
        {
            throw new StoreException("getClass", sex);
        }
        finally
        {
            JDBCUtil.close(rs);
            JDBCUtil.close(ps);
            closeConnection();
        }

        return -1;
    }

    public int setUnknownClass(String className, Integer packageId) throws StoreException
    {
        return setClass(className, packageId, null, null, null, null);
    }

    public int setClass(String className, Integer packageId, Boolean isInterface, Boolean isInner, Integer parentClassId, Integer importLocation) throws StoreException
    {
//        LOG.info("setClass: " + className + " " + importLocation + " " + packageId);
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        int classId = 0;

        try
        {
            String query = prepareSelectQuery("setClass", "select class_id, import_location_id from cdep_class where name_tx = ?");

            ps = conn.prepareStatement(query);

            ps.setString(1, className);

            rs = executeSelect(ps);

            if (rs.next())
            {
                classId = rs.getInt(1);
                if (importLocation == null)
                {
                    return classId;
                }
                rs.getInt(2);
                if (!rs.wasNull())
                {
                    return classId;
                }
            }
            JDBCUtil.close(rs);
            rs = null;
            JDBCUtil.close(ps);
            ps = null;

            if (classId == 0)
            {
                query = prepareInsertQuery("setClass", "insert into cdep_class (name_tx, package_id, interface_in, inner_in, super_id, import_location_id) values (?,?,?,?,?,?)", true);

                ps = conn.prepareStatement(query);

                ps.setString(1, className);
                if (packageId != null)
                    ps.setInt(2, packageId.intValue());
                else
                    ps.setNull(2, Types.INTEGER);
                if (isInterface != null)
                    ps.setBoolean(3, isInterface.booleanValue());
                else
                    ps.setNull(3, Types.BIT);
                if (isInner != null)
                    ps.setBoolean(4, isInner.booleanValue());
                else
                    ps.setNull(4, Types.BIT);
                if (parentClassId != null)
                    ps.setInt(5, parentClassId.intValue());
                else
                    ps.setNull(5, Types.INTEGER);
                if (importLocation != null)
                    ps.setInt(6, importLocation.intValue());
                else
                    ps.setNull(6, Types.INTEGER);

                return executeInsert(ps, true);
            }
            else
            {
                query = prepareUpdateQuery("setClass", "update cdep_class set interface_in=?,super_id=?,import_location_id=? where class_id = ?");

                ps = conn.prepareStatement(query);

                if (isInterface != null)
                    ps.setBoolean(1, isInterface.booleanValue());
                else
                    ps.setNull(1, Types.BIT);
                if (parentClassId != null)
                    ps.setInt(2, parentClassId.intValue());
                else
                    ps.setNull(2, Types.INTEGER);
                if (importLocation != null)
                    ps.setInt(3, importLocation.intValue());
                else
                    ps.setNull(3, Types.INTEGER);
                ps.setInt(4, classId);

                executeUpdate(ps);

                return classId;
            }
        }
        catch (SQLException sex)
        {
            throw new StoreException("setClass", sex);
        }
        finally
        {
            JDBCUtil.close(rs);
            JDBCUtil.close(ps);
            closeConnection();
        }
    }

    public void dropClass(String className) throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;

        try
        {
            String query = prepareDeleteQuery("dropClass", "delete from cdep_class where name_tx = ?");

            ps = conn.prepareStatement(query);

            ps.setString(1, className);

            executeDelete(ps);
        }
        catch (SQLException sex)
        {
            throw new StoreException("dropClass", sex);
        }
        finally
        {
            JDBCUtil.close(ps);
            closeConnection();
        }
    }

    public void dropClass(int classId) throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;

        try
        {
            String query = prepareDeleteQuery("dropClass", "delete from cdep_class where class_id = ?");

            ps = conn.prepareStatement(query);

            ps.setInt(1, classId);

            executeDelete(ps);
        }
        catch (SQLException sex)
        {
            throw new StoreException("dropClass", sex);
        }
        finally
        {
            JDBCUtil.close(ps);
            closeConnection();
        }
    }

    public void setClassImplements(int classId, int interfaceClassId) throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            String query = prepareSelectQuery("setClassImplements", "select 1 from cdep_class_impl where class_id = ? and interface_id = ?");

            ps = conn.prepareStatement(query);

            ps.setInt(1, classId);
            ps.setInt(2, interfaceClassId);

            rs = executeSelect(ps);

            if (rs.next())
            {
                return;
            }
            JDBCUtil.close(rs);
            rs = null;
            JDBCUtil.close(ps);
            ps = null;

            query = prepareInsertQuery("setClassImplements", "insert into cdep_class_impl (class_id, interface_id) values (?,?)", true);

            ps = conn.prepareStatement(query);

            ps.setInt(1, classId);
            ps.setInt(2, interfaceClassId);

            executeInsert(ps, true);
        }
        catch (SQLException sex)
        {
            throw new StoreException("setMethod", sex);
        }
        finally
        {
            JDBCUtil.close(rs);
            JDBCUtil.close(ps);
            closeConnection();
        }
    }

    public int getMethod(int classId, String methodName, String methodSignature) throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            String query = prepareSelectQuery("getMethod", "select method_id from cdep_method where class_id = ? and name_tx = ? and signature_tx = ?");

            ps = conn.prepareStatement(query);

            ps.setInt(1, classId);
            ps.setString(2, methodName);
            ps.setString(3, methodSignature);

            rs = executeSelect(ps);

            if (rs.next())
            {
                return rs.getInt(1);
            }
        }
        catch (SQLException sex)
        {
            throw new StoreException("getMethod", sex);
        }
        finally
        {
            JDBCUtil.close(rs);
            JDBCUtil.close(ps);
            closeConnection();
        }

        return -1;
    }

    public int setMethod(int classId, String methodName, String methodSignature) throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            String query = prepareSelectQuery("setMethod", "select method_id from cdep_method where class_id = ? and name_tx = ? and signature_tx = ?");

            ps = conn.prepareStatement(query);

            ps.setInt(1, classId);
            ps.setString(2, methodName);
            ps.setString(3, methodSignature);

            rs = executeSelect(ps);

            if (rs.next())
            {
                return rs.getInt(1);
            }
            JDBCUtil.close(rs);
            rs = null;
            JDBCUtil.close(ps);
            ps = null;

            query = prepareInsertQuery("setMethod", "insert into cdep_method (name_tx, signature_tx, class_id) values (?,?,?)", true);

            ps = conn.prepareStatement(query);

            ps.setString(1, methodName);
            ps.setString(2, methodSignature);
            ps.setInt(3, classId);

            return executeInsert(ps, true);
        }
        catch (SQLException sex)
        {
            throw new StoreException("setMethod", sex);
        }
        finally
        {
            JDBCUtil.close(rs);
            JDBCUtil.close(ps);
            closeConnection();
        }
    }

    public void dropMethod(int classId, String methodName, String methodSiguature) throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;

        try
        {
            String query = prepareDeleteQuery("dropMethod", "delete from cdep_method where class_id = ? and name_tx = ? and signature_tx = ?");

            ps = conn.prepareStatement(query);

            ps.setInt(1, classId);
            ps.setString(2, methodName);
            ps.setString(3, methodSiguature);

            executeDelete(ps);
        }
        catch (SQLException sex)
        {
            throw new StoreException("dropMethod", sex);
        }
        finally
        {
            JDBCUtil.close(ps);
            closeConnection();
        }
    }

    public void dropMethod(int methodId) throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;

        try
        {
            String query = prepareDeleteQuery("dropMethod", "delete from cdep_method where method_id = ?");

            ps = conn.prepareStatement(query);

            ps.setInt(1, methodId);

            executeDelete(ps);
        }
        catch (SQLException sex)
        {
            throw new StoreException("dropMethod(methodId)", sex);
        }
        finally
        {
            JDBCUtil.close(ps);
            closeConnection();
        }
    }

    public void setMethodCall(int methodCallerId, int methodCalleeId) throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            String query = prepareSelectQuery("setMethodCall", "select 1 from cdep_method_call where caller_id = ? and callee_id = ?");

            ps = conn.prepareStatement(query);

            ps.setInt(1, methodCallerId);
            ps.setInt(2, methodCalleeId);

            rs = executeSelect(ps);

            if (rs.next())
            {
                return;
            }

            JDBCUtil.close(rs);
            rs = null;
            JDBCUtil.close(ps);
            ps = null;

            query = prepareInsertQuery("setMethodCall", "insert into cdep_method_call (caller_id, callee_id) values (?,?)", false);

            ps = conn.prepareStatement(query);

            ps.setInt(1, methodCallerId);
            ps.setInt(2, methodCalleeId);

            executeInsert(ps, false);
        }
        catch (SQLException sex)
        {
            throw new StoreException("setMethodCall", sex);
        }
        finally
        {
            JDBCUtil.close(rs);
            JDBCUtil.close(ps);
            closeConnection();
        }
    }

    public void setClassCall(int callerClassId, int calleeClassId) throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            String query = prepareSelectQuery("setClassCall", "select 1 from cdep_class_call where caller_id = ? and callee_id = ?");

            ps = conn.prepareStatement(query);

            ps.setInt(1, callerClassId);
            ps.setInt(2, calleeClassId);

            rs = executeSelect(ps);

            if (rs.next())
            {
                return;
            }
            JDBCUtil.close(rs);
            rs = null;
            JDBCUtil.close(ps);
            ps = null;

            query = prepareInsertQuery("setClassCall", "insert into cdep_class_call (caller_id, callee_id) values (?,?)", false);

            ps = conn.prepareStatement(query);

            ps.setInt(1, callerClassId);
            ps.setInt(2, calleeClassId);

            executeInsert(ps, false);
        }
        catch (SQLException sex)
        {
            throw new StoreException("setClassCall", sex);
        }
        finally
        {
            JDBCUtil.close(rs);
            JDBCUtil.close(ps);
            closeConnection();
        }
    }

    private Object[][] getResultArray(ResultSet rs) throws SQLException
    {
        LOG.debug("getResultArray");
        List result = new ArrayList();

        ResultSetMetaData rsmd = rs.getMetaData();

        String[] columnNames = new String[rsmd.getColumnCount()];
        for (int i = 0; i < rsmd.getColumnCount(); i++)
        {
            columnNames[i] = rsmd.getColumnName(i);
        }
        result.add(columnNames);

        while (rs.next())
        {
            Object[] resultRow = new Object[rsmd.getColumnCount()];
            for (int i = 1; i < rsmd.getColumnCount()+1; i++)
            {
                switch (rsmd.getColumnType(i))
                {
                    case Types.CHAR:
                    case Types.VARCHAR:
                        resultRow[i-1] = rs.getString(i);
                        break;
                    case Types.INTEGER:
                        resultRow[i-1] = new Integer(rs.getInt(i));
                        if (rs.wasNull())
                            resultRow[i-1] = null;
                        break;
                    case Types.BIGINT:
                        resultRow[i-1] = new Long(rs.getLong(i));
                        if (rs.wasNull())
                            resultRow[i-1] = null;
                        break;
                    case Types.TIMESTAMP:
                    case Types.DATE:
                        resultRow[i-1] = rs.getDate(i);
                        break;
                    case Types.BOOLEAN:
                    case Types.BIT:
                        resultRow[i-1] = new Boolean(rs.getBoolean(i));
                        if (rs.wasNull())
                            resultRow[i-1] = null;
                        break;
                    default:
                        LOG.error("Unsupported conversion: " + rsmd.getColumnTypeName(i) +"(" + rsmd.getColumnType(i) + ") - " + rs.getObject(i));
                        throw new SQLException("Unsupported conversion: " + rsmd.getColumnTypeName(i));
                }

                LOG.debug("Adding: " + rsmd.getColumnName(i) + " - " + resultRow[i-1]);
            }

            result.add(resultRow);
        }

        return (Object[][]) result.toArray();
    }

    private List getResultList(ResultSet rs) throws SQLException
    {
        LOG.debug("getResultList");
        List result = new ArrayList();

        ResultSetMetaData rsmd = rs.getMetaData();
        while (rs.next())
        {
            Map obj = new HashMap();
            for (int i = 1; i < rsmd.getColumnCount()+1; i++)
            {
                Object info = null;
                switch (rsmd.getColumnType(i))
                {
                    case Types.CHAR:
                    case Types.VARCHAR:
                        info = rs.getString(i);
                        break;
                    case Types.INTEGER:
                        info = new Integer(rs.getInt(i));
                        if (rs.wasNull())
                            info = null;
                        break;
                    case Types.BIGINT:
                        info = new Long(rs.getLong(i));
                        if (rs.wasNull())
                            info = null;
                        break;
                    case Types.TIMESTAMP:
                    case Types.DATE:
                        info = rs.getDate(i);
                        break;
                    case Types.BOOLEAN:
                    case Types.BIT:
                        info = new Boolean(rs.getBoolean(i));
                        if (rs.wasNull())
                            info = null;
                        break;
                    default:
                        LOG.error("Unsupported conversion: " + rsmd.getColumnTypeName(i) +"(" + rsmd.getColumnType(i) + ") - " + rs.getObject(i));
                        throw new SQLException("Unsupported conversion: " + rsmd.getColumnTypeName(i));
                }

                LOG.debug("Adding: " + rsmd.getColumnName(i) + " - " + info);
                obj.put(rsmd.getColumnName(i), info);
            }

            result.add(obj);
        }

        return result;
    }

    public List queryClasses() throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            String query = prepareSelectQuery("queryClasses", "select * from cdep_class where inner_in = false and name_tx not like 'java.%' and name_tx not like 'javax.%'");

            ps = conn.prepareStatement(query);

            rs = executeSelect(ps);

            return getResultList(rs);
        }
        catch (SQLException sex)
        {
            throw new StoreException("queryClasses", sex);
        }
        finally
        {
            JDBCUtil.close(rs);
            JDBCUtil.close(ps);
            closeConnection();
        }
    }

    public List queryClassDependencies() throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            String query = prepareSelectQuery("queryClassDependencies", "select * from cdep_class_deps where target_name_tx not like 'java.%' and target_name_tx not like 'javax.%'");

            ps = conn.prepareStatement(query);

            rs = executeSelect(ps);

            return getResultList(rs);
        }
        catch (SQLException sex)
        {
            throw new StoreException("getClassDependencies", sex);
        }
        finally
        {
            JDBCUtil.close(rs);
            JDBCUtil.close(ps);
            closeConnection();
        }
    }

    public List queryClassDependencies(int classId) throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            String query = prepareSelectQuery("queryClassDependencies", "select * from cdep_class_deps where source_class_id = ? and target_name_tx not like 'java.%' and target_name_tx not like 'javax.%'");

            ps = conn.prepareStatement(query);

            ps.setInt(1, classId);
            
            rs = executeSelect(ps);

            return getResultList(rs);
        }
        catch (SQLException sex)
        {
            throw new StoreException("getClassDependencies", sex);
        }
        finally
        {
            JDBCUtil.close(rs);
            JDBCUtil.close(ps);
            closeConnection();
        }
    }

    public List queryClassMethods() throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            String query = prepareSelectQuery("queryClassMethods", "select * from cdep_class_methods");

            ps = conn.prepareStatement(query);

            rs = executeSelect(ps);

            return getResultList(rs);
        }
        catch (SQLException sex)
        {
            throw new StoreException("getClassMethods", sex);
        }
        finally
        {
            JDBCUtil.close(rs);
            JDBCUtil.close(ps);
            closeConnection();
        }
    }

    public Map queryClass(int classId) throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            String query = prepareSelectQuery("queryClass", "select * from cdep_class where class_id = ?");

            ps = conn.prepareStatement(query);

            ps.setInt(1, classId);

            rs = executeSelect(ps);

            List results = getResultList(rs);

            if (results.size() > 0)
                return (Map) results.get(0);
            return null;
        }
        catch (SQLException sex)
        {
            throw new StoreException("queryClass", sex);
        }
        finally
        {
            JDBCUtil.close(rs);
            JDBCUtil.close(ps);
            closeConnection();
        }
    }

    public Map queryMethod(int methodId) throws StoreException
    {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            String query = prepareSelectQuery("queryMethod", "select * from cdep_method where method_id = ?");

            ps = conn.prepareStatement(query);

            ps.setInt(1, methodId);

            rs = executeSelect(ps);

            List results = getResultList(rs);

            if (results.size() > 0)
                return (Map) results.get(0);
            return null;
        }
        catch (SQLException sex)
        {
            throw new StoreException("queryMethod", sex);
        }
        finally
        {
            JDBCUtil.close(rs);
            JDBCUtil.close(ps);
            closeConnection();
        }
    }

}

/* EOF */
